package com.zx.deploy.dao;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.stereotype.Repository;
import com.zx.deploy.common.utils.DBUtil2;
import com.zx.deploy.common.utils.DatetimeUtil;
import com.zx.deploy.config.DeployConfig;
import com.zx.deploy.model.DatabaseConfig;

@Repository
public class BackDbDao {

	public void readDataToFile(String tableName, String path, DeployConfig config, DatabaseConfig dbConfig)
			throws Exception {
		long totalStart = System.currentTimeMillis();
		File fileName = null;
		FileOutputStream fos = null;
		BufferedOutputStream bos = null;
		String fileAllPath = "";
		String version = "";
		DBUtil2 db = DBUtil2.getInstance(dbConfig);

		String nowDateStr = DatetimeUtil.getCurrentDateTime("yyyyMMdd_HHmmss");
		fileAllPath = path + "db_" + nowDateStr + ".sql";
		if (tableName != null) {
			fileAllPath = path + tableName + "_" + nowDateStr + ".sql";
		}
		fileName = new File(fileAllPath);
		if (!fileName.exists()) {
			fileName.createNewFile();
		}
		try {
			fos = new FileOutputStream(fileName);
			bos = new BufferedOutputStream(fos);

			List versions = db.queryForList(" select version() as version ");
			if (versions.size() > 0) {
				version = ((String) ((Map) versions.get(0)).get("version"));
			}

			StringBuffer sb = new StringBuffer();
			sb.append("/* \r\n");
			sb.append("TreeSoft Data Transfer For MySQL \r\n");
			sb.append("Source Server Version : " + version + " \r\n");
			sb.append("Source Database       : " + config.database_name + " \r\n");

			sb.append("web site: www.zx.cn \r\n");

			sb.append("Date: " + DatetimeUtil.getDefaultCurrentDateTime() + " \r\n");
			sb.append("*/ \r\n");
			sb.append(" \r\n");
			sb.append("SET FOREIGN_KEY_CHECKS=0; ");

			createFile(sb.toString(), bos);
			PermissionDao pdao = new PermissionDao();

			List<Map<String, Object>> tableList = new ArrayList<>();
			if (null == tableName) {
				tableList = pdao.getAllTables2(dbConfig, "");
			} else
				tableList = pdao.getAllTables2(dbConfig, tableName);

			String createTableSQL = "";

			int num = 0;
			List list4;
			Map TableColumnType;
			for (Map map : tableList) {
				String table_name = (String) map.get("TABLE_NAME");

				list4 = db.queryForList("show create table `" + table_name + "`");

				if (list4.size() > 0) {
					Map mapp = (Map) list4.get(0);

					createTableSQL = (String) mapp.get("Create Table");
				}

				StringBuffer sb2 = new StringBuffer();
				sb2.append(" \r\n");
				sb2.append("-- -------------------------------- \r\n");
				sb2.append("-- Table structure for `" + table_name + "`\r\n");
				sb2.append("-- -------------------------------- \r\n");
				sb2.append("DROP TABLE IF EXISTS `" + table_name + "`; \r\n");

				sb2.append(createTableSQL);
				sb2.append(";\r\n");

				TableColumnType = new HashMap();

				List<Map<String, Object>> listTableColumn = pdao.getTableColumns3(dbConfig, table_name);
				for (Map<String, Object> map3 : listTableColumn) {
					TableColumnType.put((String) map3.get("COLUMN_NAME"), (String) map3.get("DATA_TYPE"));
				}

				createFile(sb2.toString(), bos);
				++num;

				String sql1 = "select count(*) from   `" + dbConfig.databaseName + "`.`" + table_name + "`";
				int rowCount = db.executeQueryForCount(sql1);

				createFile("-- ---------------------------- \r\n", bos);
				createFile("-- Records of " + table_name + ", Total rows: " + rowCount + " \r\n", bos);
				createFile("-- ---------------------------- \r\n", bos);

				int limitFrom = 0;
				int pageSize = 20000;

				for (int yy = 0; yy < rowCount; yy += 20000) {
					String sql3 = "select  *  from  `" + dbConfig.databaseName + "`.`" + table_name + "`  LIMIT "
							+ limitFrom + "," + pageSize;

					List<Map<String, Object>> list = db.queryForList(sql3);

					String key = "";
					String values = "";
					String tempValues = "";

					limitFrom += 20000;

					for (Map<String, Object> map4 : list) {
						StringBuffer sb3 = new StringBuffer();
						sb3.append("INSERT INTO `" + table_name + "` VALUES (");

						values = "";
						for (Map.Entry entry : map4.entrySet()) {
							key = (String) entry.getKey();
							if (entry.getValue() == null) {
								values = values + "null,";
							} else if ((((String) TableColumnType.get(key)).equals("int"))
									|| (((String) TableColumnType.get(key)).equals("smallint"))
									|| (((String) TableColumnType.get(key)).equals("tinyint"))
									|| (((String) TableColumnType.get(key)).equals("integer"))
									|| (((String) TableColumnType.get(key)).equals("bit"))
									|| (((String) TableColumnType.get(key)).equals("bigint"))
									|| (((String) TableColumnType.get(key)).equals("double"))
									|| (((String) TableColumnType.get(key)).equals("float"))
									|| (((String) TableColumnType.get(key)).equals("decimal"))
									|| (((String) TableColumnType.get(key)).equals("numeric"))
									|| (((String) TableColumnType.get(key)).equals("mediumint"))) {
								values = values + entry.getValue() + ",";
							} else if ((((String) TableColumnType.get(key)).equals("binary"))
									|| (((String) TableColumnType.get(key)).equals("varbinary"))
									|| (((String) TableColumnType.get(key)).equals("blob"))
									|| (((String) TableColumnType.get(key)).equals("tinyblob"))
									|| (((String) TableColumnType.get(key)).equals("mediumblob"))
									|| (((String) TableColumnType.get(key)).equals("longblob"))) {
								byte[] ss = (byte[]) entry.getValue();

								if (ss.length == 0)
									values = values + "null,";
								else {
									values = values + "0x" + bytesToHexString(ss) + ",";
								}

							} else {
								tempValues = (String) entry.getValue();
								values = values + this.quote(tempValues) + ",";
							}
						}

						sb3.append(values.substring(0, values.length() - 1));

						sb3.append(" ); \r\n");
						createFile(sb3.toString(), bos);
						++num;

						if (num < 50000)
							continue;
						try {
							num = 0;
							bos.flush();
						} catch (IOException e) {
							e.printStackTrace();
						}

					}

				}

				if (num >= 50000) {
					System.out.println("===============清缓存一次" + num + "===========");
					try {
						num = 0;
						bos.flush();
					} catch (IOException e) {
						e.printStackTrace();
					}

				}

			}
			if (tableName != null)
				return;
			
			sb = new StringBuffer();
			List<Map<String, Object>> viewsList = pdao.getAllViews(dbConfig);
			String viewName = "";
			for (Map map : viewsList) {
				viewName = (String) map.get("TABLE_NAME");

				sb.append("-- ---------------------------- \r\n");
				sb.append("-- View structure for `" + viewName + "` \r\n");
				sb.append("-- ---------------------------- \r\n");

				sb.append("DROP VIEW IF EXISTS `" + viewName + "`; \r\n");
				sb.append("CREATE VIEW `" + viewName + "` AS " + pdao.getViewSql(viewName, dbConfig) + "; \r\n");
			}
			createFile(sb.toString(), bos);

			sb = new StringBuffer();

			List<Map<String, Object>> procsList = pdao.getAllFuntion(dbConfig);
			for (Map map : procsList) {
				String proc_name = (String) map.get("ROUTINE_NAME");

				sb.append("-- ---------------------------- \r\n");
				sb.append("-- Procedure structure for `" + proc_name + "` \r\n");
				sb.append("-- ---------------------------- \r\n");
				sb.append("DROP PROCEDURE IF EXISTS `" + proc_name + "`; \r\n");
				sb.append("DELIMITER ;; \r\n");
				sb.append(pdao.getProcSqlForSQL(proc_name, dbConfig) + " ;; \r\n");
				sb.append("DELIMITER ; \r\n");
			}
			createFile(sb.toString(), bos);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.releaseConn();
			try {
				bos.flush();
				bos.close();
				fos.close();
			} catch (IOException iox) {
				System.err.println(iox);
			}
		}
		long totalEnd = System.currentTimeMillis();
		System.out.println("----备份总耗时：" + (totalEnd - totalStart) + "毫秒," + DatetimeUtil.getDefaultCurrentDateTime());
	}

	public String bytesToHexString(byte[] src) {
		StringBuilder stringBuilder = new StringBuilder("");
		if ((src == null) || (src.length <= 0)) {
			return null;
		}
		for (int i = 0; i < src.length; ++i) {
			int v = src[i] & 0xFF;
			String hv = Integer.toHexString(v);
			if (hv.length() < 2) {
				stringBuilder.append(0);
			}
			stringBuilder.append(hv);
		}
		return stringBuilder.toString();
	}

	public void createFile(String s, BufferedOutputStream bos) {
		try {
			bos.write(s.getBytes("UTF-8"));
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
    protected String quote(String source) {
        StringBuilder buffer = new StringBuilder();
        buffer.append("\"");
        if(source != null) {
            char c;

            for(int i = 0, size = source.length(); i < size; i++) {
                c = source.charAt(i);

                switch (c) {
                    case '\\': {
                        buffer.append("\\\\"); break;
                    }
                    case '\'': {
                        buffer.append("\\\'"); break;
                    }
                    case '"': {
                        buffer.append("\\\""); break;
                    }
                    case '\r': {
                        buffer.append("\\r"); break;
                    }
                    case '\n': {
                        buffer.append("\\n"); break;
                    }
                    case '\t': {
                        buffer.append("\\t"); break;
                    }
                    case '\b': {
                        buffer.append("\\b"); break;
                    }
                    case '\f': {
                        buffer.append("\\f"); break;
                    }
                    default : {
                        buffer.append(c); break;
                    }
                }   
            }
        }

        buffer.append("\"");
        return buffer.toString();
    }

}
